package com.zaoke.web.dao.food.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.zaoke.web.bo.food.Food;
import com.zaoke.web.dao.food.FoodDao;
import com.zaoke.web.utils.DoubleFormat;

public class FoodDaoImpl extends JdbcDaoSupport implements FoodDao {

	private static final String TABLE = "`zaoke`.`food` ";
	private static final String QUERY_VIEW = "`id`, `name`, `type`, `sale_price`, `desc`, `state`, `sequence`, `add_userid`, `add_time`, `lastmod_userid`,`lastmod_time`";

	public Set<Food> getFoodsByState(Integer[] states) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE
				+ " where `state` in (";
		sql += StringUtils.join(states, ",");
		sql += ")";

		Set<Food> result = new HashSet<Food>();
		List<Food> foods = getJdbcTemplate().query(sql,
				new RowMapper<Food>() {

					public Food mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Food food = new Food();
						food.setId(rs.getLong("id"));
						food.setName(rs.getString("name"));
						food.setType(rs.getInt("type"));
						food.setSalePrice(rs.getDouble("sale_price"));
						food.setDesc(rs.getString("desc"));
						food.setState(rs.getInt("state"));
						food.setSequence(rs.getInt("sequence"));
						food.setAddUserid(rs.getLong("add_userid"));
						food.setAddTime(rs.getDate("add_time"));
						food.setLastmodUserid(rs.getLong("lastmod_userid"));
						food.setLastmodTime(rs.getDate("lastmod_time"));
						return food;
					}
				});

		if (foods != null && foods.size() > 0)
			result.addAll(foods);

		return result;
	}

	@Override
	public Food newFood(Food food) {

		if (food == null)
			return food;
		String name = food.getName();
		Integer type = food.getType();
		Double price = food.getSalePrice();
		String desc = food.getDesc();
		Integer state = food.getState();
		Long userid = food.getUserid();
		
		
	    String sql = "insert into " + TABLE; 
	    sql += "(`name`,`type`,sale_price,`desc`,state,add_userid,add_time,lastmod_userid,lastmod_time)"; 
	    sql += " values(?,?,?,?,?,?,now(),?,now())"; 
	    final Object[] params = new Object[]{name,type,DoubleFormat.format(price),desc,state,userid,userid};
		final String exesql = sql; 
	    KeyHolder holder = new GeneratedKeyHolder();
	    
	    
	    int count = getJdbcTemplate().update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(exesql);
				ps.setObject(1, params[0]);
				ps.setObject(2, params[1]);
				ps.setObject(3, params[2]);
				ps.setObject(4, params[3]);
				ps.setObject(5, params[4]);
				ps.setObject(6, params[5]);
				ps.setObject(7, params[6]);
				return ps;
			}
		}, holder);

	    if(count > 0 && holder.getKey() != null)
	    {
	    	food.setId(holder.getKey().longValue());
	    }

		return food;

	}

	@Override
	public Food getFoodById(Long foodid) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE
		+ " where `id`=?";
		Object[] params = new Object[]{foodid};
		List<Food> foods = getJdbcTemplate().query(sql,
			new RowMapper<Food>() {
	
				public Food mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					Food food = new Food();
					food.setId(rs.getLong("id"));
					food.setName(rs.getString("name"));
					food.setType(rs.getInt("type"));
					food.setSalePrice(rs.getDouble("sale_price"));
					food.setDesc(rs.getString("desc"));
					food.setState(rs.getInt("state"));
					food.setSequence(rs.getInt("sequence"));
					food.setAddUserid(rs.getLong("add_userid"));
					food.setAddTime(rs.getDate("add_time"));
					food.setLastmodUserid(rs.getLong("lastmod_userid"));
					food.setLastmodTime(rs.getDate("lastmod_time"));
					return food;
				}
		},params);

		if (foods != null && foods.size() > 0)
			return foods.get(0);
		
		return null;
	}

	@Override
	public boolean updateFood(Food food) {
		if (food == null)
			return false;
		
		String sql = "update " + TABLE + "set lastmod_time=now() ";
		Long foodid = food.getId();
		String name = food.getName();
		Double price = food.getSalePrice();
		String desc = food.getDesc();
		Integer state = food.getState();
		Long userid = food.getUserid();
		List<Object> params = new LinkedList<Object>();
		if(name!=null && !"".equals(name))
		{
			sql += " , `name`=? ";
			params.add(name);
		}
		if(price!=null)
		{
			sql += " , sale_price=? ";
			params.add(DoubleFormat.format(price));
		}
		if(desc!=null && !"".equals(desc))
		{
			sql += " , `desc`=? ";
			params.add(desc);
		}
		if(state!=null)
		{
			sql += " , state=? ";
			params.add(state);
		}
	    if(userid!=null)
	    {
	    	sql += " , lastmod_userid=? ";
	    	params.add(userid);
	    }
	    if(food.getSequence() != null){
	    	sql += " , sequence=? ";
	    	params.add(food.getSequence());
	    }
	    sql += " where id=?";
	    params.add(foodid);
	    
	    return getJdbcTemplate().update(sql, params.toArray())>0;
	    
	}

	@Override
	public boolean deleteFood(Long foodid) {
		String sql = "delete from " + TABLE + " where id=? ";
		return getJdbcTemplate().update(sql, new Object[]{foodid})>0;
	}
}
